use v6.d;
unit module Table;

use DB::SQLite;
use DB::Result;
use DB::Connection;

grammar DDL is export {
	rule TOP {
        :my  @*col-names;
	    'CREATE' 'TABLE' <table-name> \( <column-section> [\, <constraint-section> ]? \) 
        #{say @*col-names}
    }


    token table-name { \S+ }

    rule column-section { <column>+ %  ','  }
    #rule endblock {\, <constraint-section>}
    rule constraint-section { <table-constraint>+  % ','  }


    token comma { ',' }

    rule column {
        <column-name> <column-type>  [\( <size> [ \, <precision>]? \)]? <column-constraint>*
        <?{
           @*col-names.append($/<column-name>.Str) if $/<column-name>;
          #say $/<column-name>
         
          if $/<column-type> ~~
                | 'INT'
                | 'INTEGER'
                | 'TINYINT'
                | 'SMALLINT'
                | 'MEDIUMINT'
                | 'BIGINT'
                | 'INSIGNED BIG INT'
                | 'INT2'
                | 'INT8'
                | 'TEXT'
                | 'CLOB'
                | 'REAL'
                | 'DOUBLE'
                | 'DOUBLE PRECISION'
                | 'FLOAT'
                | 'BLOB'
                | 'NUMERIC'
                | 'BOOLEAN'
                | 'DATE'
                | 'DATETIME' { $/<size> ~~ Nil && $/<precision> ~~ Nil }
        elsif $/<column-type> ~~
                | 'CHARACTER'
                | 'CHAR'
                | 'VARCHAR'
                | 'VARYING CHARACTER'
                | 'NCHAR'
                | 'NVARCHAR'
                | 'NATIVE CHARACTER' { $/<size> && $/<precision> ~~ Nil }
        elsif $/<column-type> ~~ 'DECIMAL' { $/<size> && $/<precision>}
        else { False }
    }>
}
token column-type {
          | 'INT'
          | 'INTEGER'
          | 'TINYINT'
          | 'SMALLINT'
          | 'MEDIUMINT'
          | 'BIGINT'
          | 'INSIGNED BIG INT'
          | 'INT2'
          | 'INT8'
          | 'TEXT'
          | 'CLOB'
          | 'REAL'
          | 'DOUBLE'
          | 'DOUBLE PRECISION'
          | 'FLOAT'
          | 'BLOB'
          | 'NUMERIC'
          | 'BOOLEAN'
          | 'DATE'
          | 'DATETIME'
          | 'CHARACTER'
          | 'CHAR'
          | 'VARCHAR'
          | 'VARYING CHARACTER'
          | 'NCHAR'
          | 'NVARCHAR'
          | 'NATIVE' 'CHARACTER'
          | 'DECIMAL'
}
		  
        
    
token column-name { \w+ }




token size { \d+ }

token precision { \d+ }

proto rule column-constraint {*}
rule column-constraint:sym<primary-key> {
    'PRIMARY KEY'
    }
rule column-constraint:sym<default> {
    'DEFAULT' \( <defualt-value> \)
    }
token default-value { \w+ }
rule column-constraint:sym<not-null> {
    'NOT NULL'
    }
rule column-constraint:sym<unique> { 'UNIQUE'  }





rule table-constraint {
    'CONSTRAINT' <constraint-name> <constraint-type> \( <keys>  \)
    <?{  
        my $bool = True;
        for $/<keys><index-key>>>.Str -> $_ {
            unless $_ (elem) @*col-names.Seq { $bool = False}
        }
        $bool;

    }>
}
token constraint-name { \w+ }
token constraint-type {
    | 'PRIMARY  KEY' 
    | 'UNIQUE'
    | 'FOREIGN KEY'
}
rule keys {
    <index-key>+ % \,
    }
token index-key {\w+}

}


role Tabular  is export {
	
    has Str $.ddl is required is readonly; # DDL语句
    has Str $.source is required is readonly; # DSN

	# 连接数据库 
	# Praram: $source -- Data Source Name
	# Return: DB 对象
	method connect(--> DB::Connection) { ... }

	# 表名
	method table-name(--> Str) {
        my $ddl = $!ddl;
	    my $table-name = DDL.parse($ddl).<table-name>.Str || '';
	    return $table-name;
	}

	# 初始化表
	# Param: 
	# Return: Bool值 -- True | False
	method init(Str $source --> Bool) { ... }

	# 选择列
	# Param:  @columns -- 列名
	#         $where   -- 选择条件 哈希值 {$column-name => $value}
	# Return: ResultSet 对象
	method select( @columns, $where --> DB::Result) { ... }

	# 更新列
	# Param:  %hashes -- 更新的列名及值的哈希对 { $column-name => $value}
	#         $where  -- 选择条件 哈希值 { $column-name => $value }
	# Return: Int值   -- 已更新的行数
	method update(Pair $kv, Pair $where --> Int) {...}

	# 删除列
	# Param:  $where  -- 选择条件 哈希值 {$column-name => $value}
	# Return: Int值   -- 已删除的行数
	method delete(Pair $where) returns Int {...}

	# 插入数据
	# Param:  @columns -- 插入数据的列名
	#         @values  -- 插入数据的值
	# Return: Int值    -- 已插入的行数 
	method insert( Str @columns, @values --> Int) {...}

	# 关闭数据库连接
	# method finish() {
	#     self.connect.finish;
	# }
}

class Form does Tabular is export  {

	my $connection;
  
    # multi method connect(Str $source -->DB::Connection) {
    #     $connection = DB::SQLite.new(filename=>$source).db;
	# 	return $connection;
	# }

    multi method connect() {
		#$connection = DB::SQLite.new(filename=>$!source).db ;
	    return  $connection;
    }

    method init(-->Bool) {
		my $drop = sprintf("DROP TABLE IF EXISTS %s", self.table-name);
		my $dbh = self.connect;
		$dbh.execute($drop);
	    return  $dbh.execute($!ddl).defined;
    }

    method col-names() {
        DDL.parse($!ddl).<column-section><column>.>><column-name>.>>.Str
    }

    method select( @columns, Pair $where) {
        my $dbh = self.connect;
		#my $filt = $where.value ~~ Num ?? $where.value !! "'" ~ $where.value.Str ~ "'";
	    my $stmt = sprintf("SELECT %s FROM %s WHERE %s = ?", 
		                        @columns eqv ['ALL'] ?? '*' !! @columns.join(", "),
								self.table-name,
								$where.key);
	    # my $sth will leave {.finish} = $dbh.prepare($stmt);
	    return $dbh.query($stmt, $where.value);
		
    }
    
    method update(Pair $kv, Pair $where --> Int) {
        my $con = self.connect;
		my $val = $kv.value ~~ Num ?? $kv.value !! "'" ~ $kv.value.Str ~ "'";
		my $filt = $where.value ~~ Num ?? $where.value !! "'" ~ $where.value.Str ~ "'";
	    my $stmt = sprintf("UPDATE %s SET %s = %s  WHERE %s = %s", self.table-name, $kv.key, $val, $where.key,$filt);
	    #my $sth will leave {.finish} = $con.prepare($stmt);
	    $con.execute($stmt);
    }

    method delete(Pair $where --> Int) {
        my $con = self.connect;
	    my $stmt = sprintf("DELETE FROM %s WHERE %s = ?", self.table-name, $where.key);
	    #my $sth  = $con.prepare($stmt);
	    return $con.query($stmt,$where.value);
    }

    method insert( @columns, @values --> Int)  {
        my $con = self.connect;
	    my @a;
		@a.append("?") for ^@columns;
	    my $stmt = sprintf("INSERT INTO %s (%s) VALUES (%s)", self.table-name, @columns.join(", "), @a.join(", "));
	    #my $sth will leave {.finish} = $con.prepare($stmt);
	    $con.query($stmt,| @values);
    }

	method select-all() {
		my $con = self.connect;
		my $stmt = sprintf("SELECT * FROM %s", self.table-name);
		$con.query($stmt);
	}

	submethod TWEAK {$connection = DB::SQLite.new(filename=>$!source).db;}

	submethod DESTROY { $connection.free; }
 
}

